package com.lzwk.app.ods.ocpx;

/**
 * @title: CDCTables
 * @Author Yeaser
 * @Date: 2022/1/29 17:58
 * @Version 1.0
 * Description: TODO
 */
public class OdsCDCTables {

    /*public static void ocpxAccountAccount(StreamTableEnvironment tenv) {
        tenv.executeSql("create table ocpx_account_account(" +
                " `id` INT, " +
                " `openid` STRING, " +
                " `unionid` STRING, " +
                " `nickname` STRING, " +
                " `headimgurl` STRING, " +
                " `sex` INT, " +
                " `province` STRING, " +
                " `country` STRING, " +
                " `city` STRING, " +
                " `status` INT, " +
                " `is_robot` INT, " +
                " `active_ip` STRING, " +
                " `active_time` TIMESTAMP(0), " +
                " `create_time` TIMESTAMP(0), " +
                " `is_init` INT, " +
                " `src` STRING, " +
                " `parent_account_id` INT, " +
                "  PRIMARY KEY (`id`) NOT ENFORCED" +
                " )with(" +
                "   'connector'='mysql-cdc', " +
                "   'hostname'='" + ADBConstant.OCPXSLAVEHOST + "', " +
                "   'port'='" + ADBConstant.OCPXSLAVEPORT + "', " +
                "   'username'='" + ADBConstant.OCPXSLAVEUSERNAME + "', " +
                "   'password'='"+ ADBConstant.OCPXSLAVEPASSWORD + "', " +
                "   'database-name'='" + ADBConstant.OCPXSLAVEDBNAME + "', " +
                "   'table-name'='" + ADBConstant.OCPXCDCACCOUNTACCOUNT + "', " +
                "   'server-time-zone'='Asia/Shanghai', " +
                "   'debezium.snapshot.locking.mode' = 'none' " +
                ")");

        Table table = tenv.sqlQuery("select * from ocpx_account_account");

        tenv.createTemporaryView("ods_ocpx_account_account",table);

    }


    public static void ocpxAccountWxuser(StreamTableEnvironment tenv) {
        tenv.executeSql("create table ocpx_account_wxuser(" +
                " `id` INT, " +
                " `openid` STRING, " +
                " `unionid` STRING, " +
                " `headimgurl` STRING, " +
                " `sex` INT, " +
                " `nickname` STRING, " +
                " `subscribed` INT, " +
                " `create_time` TIMESTAMP(0), " +
                " `active_ip` STRING, " +
                " `active_time` TIMESTAMP(0), " +
                " `update_time` TIMESTAMP(0), " +
                " `account_id` INT, " +
                " `wxapp_id` INT, " +
                "  PRIMARY KEY (`id`) NOT ENFORCED" +
                " )with(" +
                "   'connector'='mysql-cdc', " +
                "   'hostname'='" + ADBConstant.OCPXSLAVEHOST + "', " +
                "   'port'='" + ADBConstant.OCPXSLAVEPORT + "', " +
                "   'username'='" + ADBConstant.OCPXSLAVEUSERNAME + "', " +
                "   'password'='"+ ADBConstant.OCPXSLAVEPASSWORD + "', " +
                "   'database-name'='" + ADBConstant.OCPXSLAVEDBNAME + "', " +
                "   'table-name'='" + ADBConstant.OCPXJDBCACCOUNTWXUSER + "', " +
                "   'server-time-zone'='Asia/Shanghai', " +
                "   'debezium.snapshot.locking.mode' = 'none' " +
                ")");

        Table table = tenv.sqlQuery("select * from ocpx_account_wxuser");

        tenv.createTemporaryView("ods_ocpx_account_wxuser",table);

    }*/

/*    public static void ocpxPublicAdminRecordorgref(StreamExecutionEnvironment env) {
        StreamTableEnvironment tenv = StreamTableEnvironment.create(env);
        tenv.executeSql("create table ocpx_public_admin_recordorgref(" +
                " `id` INT, " +
                " `object_id` INT, " +
                " `object_type` STRING, " +
                " `org_id` INT, " +
                " `creator` INT, " +
                " `create_time` TIMESTAMP(0), " +
                " `update_time` TIMESTAMP(0), " +
                "  PRIMARY KEY (`id`) NOT ENFORCED" +
                " )with(" +
                "   'connector'='mysql-cdc', " +
                "   'hostname'='10.1.192.67', " +
                "   'port'='3306', " +
                "   'username'='ocpx_galaxy', " +
                "   'password'='FLsdf123', " +
                "   'database-name'='ocpx_elf', " +
                "   'table-name'='public_admin_recordorgref', " +
                "   'server-time-zone'='Asia/Shanghai', " +
                "   'debezium.snapshot.locking.mode' = 'none' " +
                ")");

        Table table = tenv.sqlQuery("select * from ocpx_public_admin_recordorgref");

        tenv.createTemporaryView("ods_ocpx_account_wxuser",table);

        tenv
                .toRetractStream(table, OdsPublicAdminRecordorgref.class)
                .filter(data -> data.f0)
                .map(value -> value.f1)
                .addSink(MyKafkaUtil.getKafkaProduce(ADBConstant.ODSOCPXPUBLICADMINRECORDORGREF));
    }*/

/*    public static void dimAdbAccountMapping(StreamExecutionEnvironment env) {
        StreamTableEnvironment tenv = StreamTableEnvironment.create(env);
        tenv.executeSql("create table dim_adb_account_mapping(" +
                "  `id` STRING, " +
                "  `object_id` STRING, " +
                "  `object_type` STRING, " +
                "  `account_id` INT, " +
                "  `state` STRING, " +
                "  `remark` STRING, " +
                "  `create_time` TIMESTAMP(0), " +
                "  `update_time` TIMESTAMP(0), " +
                "  PRIMARY KEY (`id`) NOT ENFORCED" +
                " )with(" +
                "   'connector'='mysql-cdc', " +
                "   'hostname'='" + ADBConstant.ADBSLAVEHOST + "', " +
                "   'port'='" + ADBConstant.ADBSLAVEPORT + "', " +
                "   'username'" + ADBConstant.ADBSLAVEUSERNAME + "', " +
                "   'password' "+ ADBConstant.ADBSLAVEPASSWORD + "', " +
                "   'database-name'=' "+ ADBConstant.ADBSLAVEDBNAME + "', " +
                "   'table-name'=' "+ ADBConstant.ADBSLAVETABLEACCOUNTMAPPING + "', " +
                "   'server-time-zone'='Asia/Shanghai', " +
                "   'debezium.snapshot.locking.mode' = 'none' " +
                ")");

        Table table = tenv.sqlQuery("select " +
                " cast(`object_id` AS INT), " +
                " `object_type`, " +
                " if(`account_id` is null, -1, `account_id`) as `account_id`, " +
                " if(`state` is null, '', REPLACE(REPLACE(`state`,'\\n',''),'\\t','')) as `state`, " +
                " if(`remark` is null, '', REPLACE(REPLACE(`remark`,'\\n',''),'\\t','')) as `remark`, " +
                " `create_time`, " +
                " `update_time`, " +
                " `id` " +
                " from dim_adb_account_mapping " +
                "");

        tenv.createTemporaryView("dim_adb_account_mapping",table);

    }


    public static void dimAdbDivisionActivity(StreamExecutionEnvironment env) {
        StreamTableEnvironment tenv = StreamTableEnvironment.create(env);
        tenv.executeSql("create table dim_adb_division_activity(" +
                "  `id` INT, " +
                "  `liveroom_id` INT, " +
                "  `parent_id` INT, " +
                "  `grade` INT, " +
                "  `top` INT, " +
                "  `activity_name` STRING, " +
                "  `activity_url` STRING, " +
                "  `description` STRING, " +
                "  `state` STRING, " +
                "  `create_time` TIMESTAMP(0), " +
                "  `update_time` TIMESTAMP(0), " +
                "  PRIMARY KEY (`id`) NOT ENFORCED" +
                " )with(" +
                "   'connector'='mysql-cdc', " +
                "   'hostname'='" + ADBConstant.ADBSLAVEHOST + "', " +
                "   'port'='" + ADBConstant.ADBSLAVEPORT + "', " +
                "   'username'" + ADBConstant.ADBSLAVEUSERNAME + "', " +
                "   'password' "+ ADBConstant.ADBSLAVEPASSWORD + "', " +
                "   'database-name'=' "+ ADBConstant.ADBSLAVEDBNAME + "', " +
                "   'table-name'=' "+ ADBConstant.ADBSLAVETBALEDIVISIONACTIVITY + "', " +
                "   'server-time-zone'='Asia/Shanghai', " +
                "   'debezium.snapshot.locking.mode' = 'none' " +
                ")");

        Table table = tenv.sqlQuery("select " +
                " `id`, " +
                " `liveroom_id`, " +
                " if(`parent_id` is null, -1, `parent_id`) as `parent_id`, " +
                " `grade`, " +
                " `top`, " +
                " REPLACE(REPLACE(`activity_name`,'\\n',''),'\\t','') as `activity_name`, " +
                " REPLACE(REPLACE(`activity_url`,'\\n',''),'\\t','') as `activity_url`, " +
                " if(`description` is null, '', REPLACE(REPLACE(`description`,'\\n',''),'\\t','')) as `description`, " +
                " if(`state` is null, '', REPLACE(REPLACE(`state`,'\\n',''),'\\t','')) as `state`, " +
                " `create_time` " +
                " `update_time` " +
                " from dim_adb_division_activity " +
                "");

        tenv.createTemporaryView("dim_adb_division_activity",table);
        */

    }
